﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FWPDocumentation.Common.DbTools;

namespace FWPDocumentation.Entities
{
    public class Group
    {
        public const String TABLE_NAME = "`fvp`.`group`";
        public const String ID = "`idGroup`";
        public const String ID_NAME = " `Group`";
        public const String PARENT_ID = "`Faculty_idFaculty`";
        public int idGroup { get; set; }
        public String nameGroup { get; set; }
        public int idFaculty { get; set; }

        public Group(){}

        private Group(int id, String name , int parentId)
        {
            this.idFaculty = parentId;
            this.nameGroup = name;
            this.idGroup = id;
        }

        public static Group CreateGroup(String name, int idFac)
        {
            var insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name+"' AND "+PARENT_ID+"='"+idFac+"'");
            int returnedID = insertCommand.ExecuteScalar<int>();
            if (returnedID != 0)
            {
                return null;
            }
            insertCommand = new SqlCommand(
                "INSERT INTO " + TABLE_NAME + "(" + ID_NAME+","+PARENT_ID + ")VALUES('" + name+"','"+idFac + "');");
            insertCommand.ExecuteNonQuery();
            insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name + "' AND " + PARENT_ID + "='" + idFac + "'");
            returnedID = insertCommand.ExecuteScalar<int>();
            return new Group(returnedID,name,idFac);
        }

        public override String ToString()
        {
            return nameGroup;
        }

        public Faculty getFaculty()
        {

            var insertCommand = new SqlCommand("SELECT idFaculty,Faculty as nameFaculty, Institute_idInstitute as idInstitute FROM " + Faculty.TABLE_NAME + " WHERE " + Faculty.ID + "=" + idFaculty);
            return insertCommand.ExecuteScalar<Faculty>();

        }


    }
}
